﻿using Hxj.Data;
using RecruitStudents.Utility;
using RecruitStudents.Utility.Const;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitStudents.Client.DAL
{
    public class DALLottery : Base.BaseService
    {
        public DataTable GetTB_Pad_SignedDataTable_New(string strExamPlaceNo, string strExamLevel, string strSpecialityKindsNO, string strExamYear
            , string strIdCard, string strName)
        {
            string strSql = @"select a.*,b.LotNO,b.LotTime,b.AllowExamID from TB_SignUpStudents as a 
                inner join (
                    select distinct a.SignUpID,b.LotNO, b.CreateTiime as LotTime,a.AllowExamID from TB_Exam_SignUpStudentsSpeciality as a left join 
                    TB_Exam_Signed as b on a.SignUpStudentsSpecialityID = b.SignUpStudentsSpecialityID  and b.SignedTime is null
                    where  a.ExamPlaceNO = '{0}' and a.ExamLevel = '{1}' and a.ExamYear='{3}' and a.SpecialityKindsNO in ({2}) and ISNULL(b.LotNO, '') != '' 
                ) as b on a.SignUpID = b.SignUpID where a.ExamYear='{3}'
            ";
            if (!strIdCard.IsNullOrEmptyString())
            {
                strSql += " and a.IdentityCard='" + strIdCard + "'";
            }
            if (!strName.IsNullOrEmptyString())
            {
                strSql += " and a.Name like '%"+ strName + "%'";
            }
            strSql = string.Format(strSql, strExamPlaceNo, strExamLevel, strSpecialityKindsNO, strExamYear);
            return GetTable(strSql);
        }
        public DataTable CheckTB_Pad_Signed_New(string SpecialityKindsNO, string ExamLevel, string examArea, string idCard, string strExamYear)
        {
            string strSql = @"select a.*,b.AllowExamID,b.LotNO,b.LotTime,c.DicName as ExamProvinceName from TB_SignUpStudents as a 
                left join (
                    select distinct a.SignUpID, a.AllowExamID,b.LotNO, b.CreateTiime as LotTime 
                     from TB_Exam_SignUpStudentsSpeciality as a left join TB_Exam_Signed as b on a.SignUpStudentsSpecialityID = b.SignUpStudentsSpecialityID 
                    where  a.ExamPlaceNO = '{0}' and a.ExamLevel = '{1}' and a.ExamYear='{4}'  and a.SpecialityKindsNO in ({2}) 
                ) as b on a.SignUpID = b.SignUpID 
                left join TB_Dic_ExamProvince as c on c.DicNO = a.ExamProvince 
                where a.IdentityCard='{3}' and a.ExamYear='{4}' and ISNULL(b.AllowExamID,'')<>'' 
            ";
            strSql = string.Format(strSql, examArea, ExamLevel, SpecialityKindsNO, idCard, strExamYear);
            return GetTable(strSql);

        }

        public DataTable GetTB_Pad_SignedDataTable(string strGroupNO, string strExamLevel, string strSpecialityKindsNO)
        {
            // TB_Exam_SignUpStudentsSpeciality._.AllowExamID.IsNull("") != "" &&
            var wcp = TB_Exam_SignUpStudentsSpeciality._.IsActive == "1"
                && (TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO == strSpecialityKindsNO)
                && (TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First == strGroupNO
                    || TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second == strGroupNO
                    || TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third == strGroupNO
                    || TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fourth == strGroupNO
                    || TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth == strGroupNO
                    || TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth == strGroupNO
                )
               && (TB_Exam_Signed._.SignedID.SubQueryNotIn(DbHelper.GetDatabase().From<TB_Pad_Queue>().Select(TB_Pad_Queue._.SignedID)));
            wcp = wcp.And(TB_Exam_SignUpStudentsSpeciality._.ExamLevel == strExamLevel);
            return DbHelper.GetDatabase().From<TB_Exam_SignUpStudentsSpeciality>() //选择表
                                       .LeftJoin<TB_SignUpStudents>(TB_SignUpStudents._.SignUpID == TB_Exam_SignUpStudentsSpeciality._.SignUpID)
                                       .LeftJoin<TB_Exam_Signed>(TB_Exam_Signed._.SignUpStudentsSpecialityID == TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID
                                       && TB_Exam_SignUpStudentsSpeciality._.ExamLevel == strExamLevel)
                                       .LeftJoin<TB_Sys_SpecialityKinds>(TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO == TB_Sys_SpecialityKinds._.SpecialityKindsNO
                                       && TB_Exam_SignUpStudentsSpeciality._.ExamLevel == strExamLevel)
                                       .Select(TB_Exam_Signed._.SignedID, TB_SignUpStudents._.All, TB_Exam_Signed._.LotNO, TB_Exam_Signed._.CreateTiime.As("LotDateTime"),
                                       TB_Exam_SignUpStudentsSpeciality._.AllowExamID, TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO,
                                       TB_Sys_SpecialityKinds._.SpecialityKindsName, TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip(TB_Exam_Signed._.LotNO, OrderByOperater.ASC))   //排序
                                       .ToDataTable();   //返回List
        }

        public DataTable CheckTB_Pad_Signed(string SpecialityKindsNO, string ExamLevel, string examArea, string idCard)
        {
            var wcp = TB_Exam_SignUpStudentsSpeciality._.IsActive == "1" && TB_SignUpStudents._.IdentityCard == idCard
                  && TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO == SpecialityKindsNO;
            wcp = wcp.And(TB_Exam_SignUpStudentsSpeciality._.ExamLevel == ExamLevel);
            var selectField = new List<Hxj.Data.Field>();
            selectField.Add(TB_SignUpStudents._.All);
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO);
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID);
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.ExamLevel);
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AllowExamID);
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.ExamPlaceNO);
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsActive);
            selectField.Add(TB_Exam_Signed._.GroupNO);
            selectField.Add(TB_Exam_Signed._.CreateTiime.As("LotTime"));
            selectField.Add(TB_Exam_Signed._.EntretenimientohID);
            selectField.Add(TB_Exam_Signed._.LotNO);
            selectField.Add(TB_Exam_Signed._.SignedID);
            selectField.Add(TB_Dic_ExamProvince._.DicName.As("ExamProvinceName"));
            if (ConstExamLevel.CS_Value == Convert.ToInt32(ExamLevel))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First.As("AppointmentGroupTime"));
            }
            else if (ConstExamLevel.FS_Value == Convert.ToInt32(ExamLevel))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Second.As("AppointmentGroupTime"));

            }
            else if (ConstExamLevel.SS_Value == Convert.ToInt32(ExamLevel))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Third.As("AppointmentGroupTime"));

            }
            else if (ConstExamLevel.SSS_Value == Convert.ToInt32(ExamLevel))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fourth.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fourth.As("AppointmentGroupTime"));

            }
            else if (ConstExamLevel.WWS_Value == Convert.ToInt32(ExamLevel))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fifth.As("AppointmentGroupTime"));

            }
            else if (ConstExamLevel.LLS_Value == Convert.ToInt32(ExamLevel))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Sixth.As("AppointmentGroupTime"));

            }
            else
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First.As("AppointmentGroupTime"));
                wcp = wcp.And(TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID.IsNull());
            }
            return DbHelper.GetDatabase().From<TB_SignUpStudents>() //选择表
                                         .LeftJoin<TB_Exam_SignUpStudentsSpeciality>(TB_SignUpStudents._.SignUpID == TB_Exam_SignUpStudentsSpeciality._.SignUpID)
                                         .LeftJoin<TB_Exam_Signed>(TB_Exam_Signed._.SignUpStudentsSpecialityID == TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID)
                                         .LeftJoin<TB_Dic_ExamProvince>(TB_Dic_ExamProvince._.DicNO == TB_SignUpStudents._.ExamProvince)
                                         .Select(selectField.ToArray())  //设置选择的字段数目 
                                         .Where(wcp)   //条件
                                         .OrderBy(new OrderByClip(TB_Exam_Signed._.LotNO, OrderByOperater.ASC))   //排序
                                         .ToDataTable();   //返回List
        }
        public DataTable GetPad_Queue_PrintDataTable(string ExamLevel, WhereClip wcp)
        {
            var selectField = new List<Hxj.Data.Field>();
            if (ConstExamLevel.CS_Value.Equals((Convert.ToInt32(ExamLevel))))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First.As("AppointmentGroupTime"));
            }
            else if (ConstExamLevel.FS_Value.Equals((Convert.ToInt32(ExamLevel))))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Second.As("AppointmentGroupTime"));
            }
            else if (ConstExamLevel.SS_Value.Equals((Convert.ToInt32(ExamLevel))))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Third.As("AppointmentGroupTime"));
            }
            else if (ConstExamLevel.SSS_Value.Equals((Convert.ToInt32(ExamLevel))))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Third.As("AppointmentGroupTime"));
            }
            else if (ConstExamLevel.WWS_Value.Equals((Convert.ToInt32(ExamLevel))))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fifth.As("AppointmentGroupTime"));
            }
            else if (ConstExamLevel.LLS_Value.Equals((Convert.ToInt32(ExamLevel))))
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Sixth.As("AppointmentGroupTime"));
            }
            else
            {
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
                selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroupTime"));
            }
            selectField.Add(TB_Pad_Queue._.All);

            return DbHelper.GetDatabase().From<TB_Pad_Queue>()
                    .LeftJoin<TB_Exam_Signed>(TB_Pad_Queue._.SignedID == TB_Exam_Signed._.SignedID)
                    .LeftJoin<TB_Exam_SignUpStudentsSpeciality>(TB_Exam_Signed._.SignUpStudentsSpecialityID == TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID)
                    .Select(selectField.ToArray())  //设置选择的字段数目 
                    .Where(wcp)   //条件
                    .OrderBy(new OrderByClip(TB_Pad_Queue._.LotNO, OrderByOperater.ASC))   //排序
                    .ToDataTable();

        }
        public DataTable GetAppointmentGroupDataTable(string strWhere, string strWhereExamLevel)
        {
            string strSql = @"SELECT GroupNo, GroupName, ExamPlaceNO, SpecialityKindsNO, TotalAcount,
                                ActualAcount, OpenAcount, GroupName + '(' + CAST(ActualAcount AS VARCHAR)+ '/' + CAST(OpenAcount AS VARCHAR) + ')' AS DisplayText,
                                 OpenAcount - ActualAcount AS IsFull, Sort,ExamLevel
                            FROM         
                            (
                                SELECT GroupNo, GroupName, ExamPlaceNO, SpecialityKindsNO, TotalAcount,ExamLevel, OpenAcount, Sort,
                                                  (SELECT     COUNT(1) AS iCount FROM dbo.TB_Exam_SignUpStudentsSpeciality AS T2 WHERE ({1})) AS ActualAcount                                                    
                                FROM   dbo.TB_Sys_ExamPlaceSpecialityKindsGroupMapping AS T1
                                WHERE  (IsActive = '1')
                             ) AS T
                             where {0}
            ";
            return DbHelper.GetDatabase().FromSql(string.Format(strSql, strWhere, strWhereExamLevel)).ToDataTable();
        }
    }
}
